package com.question.mapper;

import com.question.annotation.AutoFill;
import com.question.enumeration.OperationType;
import com.question.pojo.entity.Comment;
import com.question.pojo.vo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface CommentMapper {

    /**
     * 添加评论
     * @param comment
     */
    @Insert("INSERT INTO question_comments (question_id, user_id, content) " +
            "VALUES (#{questionId}, #{userId}, #{content})")
    @AutoFill(OperationType.CREATED_AT)
    void insertComment(Comment comment);

    /**
     * 根据问题查询评论列表
     * @param questionId
     * @return
     */
    @Select("SELECT c.comment_id, c.content, c.created_at, " +
            "u.user_id, u.username, u.is_admin " +
            "FROM question_comments c " +
            "JOIN user u ON c.user_id = u.user_id " +
            "WHERE c.question_id = #{questionId} " +
            "GROUP BY c.comment_id, c.content, c.created_at, u.user_id, u.username, u.is_admin " +
            "ORDER BY c.created_at DESC")
    List<CommentVO> selectCommentsByQuestionId(Integer questionId);

    /**
     * 根据评论ID查询评论
     * @param commentId
     * @return
     */
    @Select("SELECT * FROM question_comments WHERE comment_id = #{commentId}")
    Comment findCommentById(Integer commentId);

   /**
     * 根据评论ID删除评论
     * @param commentId
     */
    @Delete("DELETE FROM question_comments WHERE comment_id = #{commentId}")
    void deleteCommentById(Integer commentId);






   /**
     * 管理后台查询评论列表
     * @param keyword
     * @param majorId
     * @param offset
     * @param pageSize
     * @return
     */
   @Select("<script>" +
           "SELECT c.comment_id, c.content, c.created_at, " +
           "u.user_id, u.username, u.email, u.professional, " +
           "q.content AS question_content, " +
           "m.major_name AS question_major_name, " + // 新增问题专业名字段
           "(SELECT COUNT(*) FROM comment_likes cl WHERE cl.comment_id = c.comment_id) AS like_count " +
           "FROM question_comments c " +
           "JOIN user u ON c.user_id = u.user_id " +
           "JOIN questions q ON c.question_id = q.question_id " +
           "JOIN question_categories qc ON q.category_id = qc.category_id " +
           "JOIN majors m ON qc.major_id = m.major_id " +
           "<where>" +
           "   <if test='keyword != null and keyword != \"\"'>" +
           "       AND (c.content LIKE CONCAT('%', #{keyword}, '%') OR q.content LIKE CONCAT('%', #{keyword}, '%'))" +
           "   </if>" +
           "   <if test='majorId != null'>" +
           "       AND m.major_id = #{majorId}" +
           "   </if>" +
           "</where>" +
           "ORDER BY c.created_at DESC " +
           "LIMIT #{offset}, #{pageSize}" +
           "</script>")
        @Results(id = "adminCommentMap", value = {
        @Result(property = "commentId", column = "comment_id", id = true),
        @Result(property = "content", column = "content"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "username", column = "username"),
        @Result(property = "email", column = "email"),
        @Result(property = "professional", column = "professional"),
        @Result(property = "questionContent", column = "question_content"),
        @Result(property = "likeCount", column = "like_count"),
        @Result(property = "questionMajorName", column = "question_major_name") // 新增字段映射
        })
        List<AdminCommentVO> selectAdminCommentList(
        @Param("keyword") String keyword,
        @Param("majorId") Integer majorId,
        @Param("offset") Integer offset,
        @Param("pageSize") Integer pageSize);

        /**
        * 统计管理后台评论数量
        * @param keyword 搜索关键词
        * @param majorId 专业ID
        * @return 评论总数
        */
        @Select("<script>" +
                "SELECT COUNT(*) " +
                "FROM question_comments c " +
                "JOIN user u ON c.user_id = u.user_id " +
                "JOIN questions q ON c.question_id = q.question_id " +
                "JOIN question_categories qc ON q.category_id = qc.category_id " +
                "JOIN majors m ON qc.major_id = m.major_id " +  // 添加专业表连接
                "<where>" +
                "   <if test='keyword != null and keyword != \"\"'>" +
                "       AND (c.content LIKE CONCAT('%', #{keyword}, '%') OR q.content LIKE CONCAT('%', #{keyword}, '%'))" +
                "   </if>" +
                "   <if test='majorId != null'>" +
                "       AND m.major_id = #{majorId}" +  // 使用问题所属专业进行筛选
                "   </if>" +
                "</where>" +
                "</script>")
        Integer countAdminCommentList(
                @Param("keyword") String keyword,
                @Param("majorId") Integer majorId);

        /**
        * 获取专业评论分布
        * @return 专业分布列表
        */
        @Select("SELECT m.major_id, m.major_name AS majorName, COUNT(c.comment_id) AS count " +
                "FROM question_comments c " +
                "JOIN questions q ON c.question_id = q.question_id " +
                "JOIN question_categories qc ON q.category_id = qc.category_id " +
                "JOIN majors m ON qc.major_id = m.major_id " +
                "GROUP BY m.major_id, m.major_name " +
                "ORDER BY count DESC")
        @Results({
                @Result(property = "majorId", column = "major_id"),
                @Result(property = "majorName", column = "majorName"),
                @Result(property = "count", column = "count")
        })
        List<MajorDistributionVO> selectMajorDistribution();

        /**
        * 获取高频评论题目
        * @param limit 限制数量
        * @return 热门题目列表
        */
        @Select("SELECT q.question_id, q.content, COUNT(c.comment_id) AS commentCount " +
                "FROM questions q " +
                "LEFT JOIN question_comments c ON q.question_id = c.question_id " +
                "GROUP BY q.question_id " +
                "ORDER BY commentCount DESC " +
                "LIMIT #{limit}")
        @Results({
                @Result(property = "questionId", column = "question_id"),
                @Result(property = "content", column = "content"),
                @Result(property = "commentCount", column = "commentCount")
        })
        List<HotQuestionVO> selectHotQuestions(Integer limit);

        /**
        * 获取评论点赞用户
        * @param commentId 评论ID
        * @return 点赞用户列表
        */
        @Select("SELECT u.user_id, u.username, u.email, u.professional " +
                "FROM comment_likes cl " +
                "JOIN user u ON cl.user_id = u.user_id " +
                "WHERE cl.comment_id = #{commentId}")
        @Results({
                @Result(property = "userId", column = "user_id"),
                @Result(property = "username", column = "username"),
                @Result(property = "email", column = "email"),
                @Result(property = "professional", column = "professional")
        })
        List<LikeUserVO> selectLikeUsers(Integer commentId);

        /**
        * 统计总评论数
        * @return 评论总数
        */
        @Select("SELECT COUNT(*) FROM question_comments")
        int countTotalComments();

        /**
        * 统计活跃用户数
        * @param days 最近天数
        * @return 活跃用户数
        */
        @Select("SELECT COUNT(DISTINCT user_id) FROM question_comments WHERE created_at >= DATE_SUB(NOW(), INTERVAL #{days} DAY)")
        int countActiveUsers(@Param("days") int days);

        /**
        * 统计总点赞数
        * @return 点赞总数
        */
        @Select("SELECT COUNT(*) FROM comment_likes")
        int countTotalLikes();



    /**
     * 获取用户统计列表
     * @param keyword
     * @param majorId
     * @param offset
     * @param pageSize
     * @return
     */
    @Select("<script>" +
            "SELECT u.user_id, u.username, u.email, u.professional, " +
            "COUNT(c.comment_id) AS commentCount, " +
            "COALESCE(SUM(cl.like_count), 0) AS receivedLikes " +
            "FROM user u " +
            "LEFT JOIN question_comments c ON u.user_id = c.user_id " +
            "LEFT JOIN questions q ON c.question_id = q.question_id " + // 新增：关联问题表
            "LEFT JOIN question_categories qc ON q.category_id = qc.category_id " + // 新增：关联分类表
            "LEFT JOIN (SELECT comment_id, COUNT(*) AS like_count FROM comment_likes GROUP BY comment_id) cl " +
            "ON c.comment_id = cl.comment_id " +
            "<where>" +
            "   <if test='keyword != null and keyword != \"\"'>" +
            "       AND (u.username LIKE CONCAT('%', #{keyword}, '%') OR u.email LIKE CONCAT('%', #{keyword}, '%'))" +
            "   </if>" +
            "   <if test='majorId != null'>" +
            "       AND qc.major_id = #{majorId}" + // 修改：使用问题分类对应的专业ID
            "   </if>" +
            "</where>" +
            "GROUP BY u.user_id " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    @Results(id = "userStatMap", value = {
            @Result(property = "userId", column = "user_id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "professional", column = "professional"),
            @Result(property = "commentCount", column = "commentCount"),
            @Result(property = "receivedLikes", column = "receivedLikes")
    })
    List<UserStatVO> selectUserStatList(
            @Param("keyword") String keyword,
            @Param("majorId") Integer majorId,
            @Param("offset") Integer offset,
            @Param("pageSize") Integer pageSize);


    /**
     * 统计用户数量
     *
     * @param keyword 搜索关键词
     * @param majorId
     * @return 用户总数
     */
    @Select("<script>" +
            "SELECT COUNT(DISTINCT u.user_id) " +
            "FROM user u " +
            "LEFT JOIN question_comments c ON u.user_id = c.user_id " +
            "LEFT JOIN questions q ON c.question_id = q.question_id " + // 新增：关联问题表
            "LEFT JOIN question_categories qc ON q.category_id = qc.category_id " + // 新增：关联分类表
            "<where>" +
            "   <if test='keyword != null and keyword != \"\"'>" +
            "       AND (u.username LIKE CONCAT('%', #{keyword}, '%') OR u.email LIKE CONCAT('%', #{keyword}, '%'))" +
            "   </if>" +
            "   <if test='majorId != null'>" +
            "       AND qc.major_id = #{majorId}" + // 修改：使用问题分类对应的专业ID
            "   </if>" +
            "</where>" +
            "</script>")
    Integer countUserStatList(
            @Param("keyword") String keyword,
            @Param("majorId") Integer majorId);


    /**
     * 获取专业活跃度数据
     * @return 专业活跃度列表
     */
    @Select("SELECT m.major_id, m.major_name, " +
            "COALESCE(AVG(stats.comment_count), 0) AS avg_comments " +
            "FROM majors m " +
            "LEFT JOIN (" +
            "   SELECT qc.major_id, COUNT(c.comment_id) AS comment_count " +
            "   FROM question_comments c " +
            "   JOIN questions q ON c.question_id = q.question_id " +
            "   JOIN question_categories qc ON q.category_id = qc.category_id " +
            "   GROUP BY qc.major_id, c.user_id" +
            ") stats ON m.major_id = stats.major_id " +
            "GROUP BY m.major_id, m.major_name " +
            "ORDER BY avg_comments DESC")
    @Results({
            @Result(property = "majorId", column = "major_id"),
            @Result(property = "majorName", column = "major_name"),
            @Result(property = "avgComments", column = "avg_comments")
    })
    List<MajorActivityVO> selectMajorActivity();
}

